ajDBRunSQL function
Available since AlchemyJ v4.1
Description
The ajDBRunSQL function executes a SQL statement and returns the result. Please take note that to run this function from Excel, you would need to set up the Data Source Connection in ##ExternalResources.
Syntax
ajDBRunSQL(sql_statement, [parameter], [return_type], [transpose], [return_header], [data_source_id], [convert_to_text], [run_condition],[run_by_function_point_only])
| Argument Name | Argument Type | Description |
|---|---|---|
| sql_statement (required) | Range / Array | The range of cells to form the SQL statement to be executed. The range of cells can span across multiple rows and columns. Please take note that only a single SQL statement is supported. For example, this function does not support using “;” to combine multiple SQL statements. For security best practices, it is recommended to use ‘?’ (Oracle only) placeholder in the query string. Such placeholders will be replaced with parameters from the ‘parameter’ argument. This can prevent SQL injection attack as data will be sanitized. |
| parameter (optional) | Range / Array | The range of cells to form the list of input and output parameters. Refer to the SQLParameters Snippet to see how to use this snippet to define the required fields. |
| return_type (optional) | Double | 0 indicates the function will return a set of records (in cell array). 1 indicates the function will return the output parameters. For PostgreSQL, the returning keyword will return a set of records, so should use 0 for this case. 2 indicates to return the number of the affected records. The default value is 0. |
| transpose (optional) | Boolean | If it equals TRUE, the return result will be transposed. This only affects the return that has multiple cells. The default value is FALSE. |
| return_header (optional) | Boolean | If it equals TRUE, the return result will include the column headers as the first row. This only applies when ‘return_type’ is 0 or 1. If it equals FALSE, the return result will contain data only. The default value is FALSE. |
| data_source_id (optional) | String | The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary". |
| convert_to_text (optional) | Boolean | If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE. |
| run_condition (optional) | Boolean | The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE. |
| run_by_function_point_only (optional) | Boolean | If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE. |
The function will return:
- Return Value: Database operations results or the count
- Return Type: Single Value / Multiple values (array formula)
Example
Before using ExecuteSQL, the database connection must be set up in the Data Source Connection in ##ExternalResources.
Before running this function point, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.
We will use the following data in our examples. The table name is tb_customer. It has 5 columns and 5 rows.
Example 1 - Return Type is 0
The result of the function is expected to a set of record, return_type should be 0. In this sample, input the sql statement in cell B2 .Return a set of record with column name since return_header is TRUE, and the default value of return_type is 0.
Example 2 - Return Type is 1
Some databases support returning value while inserting, updating, deleting data, such as Oracle database.The result of the function is expected to return the value, return_type should be 1.
Click AlchemyJ ribbon Insert Snippet and select SQL Parameters . A table will be inserted.
The filter condition value in the sql stetement uses ? as a placeholder, and type filter condition in SQL Parameters table.
Example 3 - Return Type is 2
The result of the function is expected to return the number of the affected records, return_type should be 2.
Example 4 - Parameter Separators
The separator symbols is configured in %%AppConfig worksheet.
The value of parameter is as below, use the value of parameter-separator to separator parameter name, and use the value of parameter-type-separator to separator parameter name,value,type and direction.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).
| Error Scenario |
|---|
| Invalid SQL Statement. |
| Invalid return type, it must be 0, 1, or 2. |
| Return header is TRUE, but return type is not 0 or 1. |
| For PostgreSQL, the return type is 0 or 2 |
| DB connection error. |